Logo


Fibonacci in Excel

by Donny Thurston


4. Generate a Fibonnaci sequence in the first column using f(0) = 1, f(1) = 1,

f(n) = f(n-1) + f(n-2)

a. Construct the ratio of each pair of adjacent terms in the Fibonnaci sequence. What happens as n increases? What about the ratio of every second term? etc.

          b. Explore sequences where f(0) and f(1) are some arbitrary integers other than 1. If f(0)=1 and f(1) = 3, then your sequence is a Lucas Sequence. All such sequences, however, have the same limit of the ratio of successive terms.


For this write-up, we are going to explore how technology can help us better understand and examine the Fibonnaci sequence. Specifically, we will be using Excel to run calculations for us, so that we can see if any interesting patterns, without having to spend great amounts of effort running calculations. Let us go ahead and observe the table that is formed when we create the Fibonnaci sequence, and oberve a couple of ratios as described in the above problem.

Well, we can definitely see some interesting patterns here. For starters, the ratio of the adjacent terms appear to limit to a particular value. This value is Phi (Φ) or sometimes is referred to as the Golden Ratio.

Since the ratio is really a limit, we can present this relationship as following:

This is already pretty neat on its own, but we don't have to stop here. Recursive functions are often fun because we are able to use the recursive definitions to discover other, interesting things about these functions. Lets use the recursive definition of f(n) to see if we can learn anythng more about Phi. Recall that f(n) = f(n-1) + f(n-2). Therefore:

Or, as n approaches infinity we can simply consider:

This may not seem all that interesting until we discover this:

Now, note that , as n approaches infinity thanks to the recursive nature of the of the fibonnaci sequence. This would suggest that:

which is a quadratic equation that, when solved for Phi, produces:

This is the limit that we are seeing in the table, and the approximate value for Phi. Note that the negative answer from solving the quadratic equation is not a possible solution because it is negative. All of the terms in fibonacci's sequence must be positive, so there won't be any negative ratios.

But what about the ratio of every other term? It looks like Phi plus one, but can we prove that it is? Lets keep playing with our functions.

Remember that we are talking about these terms as n approaches infinity.


 

So that what about the Lucas sequence as described in the question above? Will that make any difference?

What do we notice here? The terms of the Fibonnaci sequence are different, but the ratios are all the same! Why is that?

Well, to start, in the above proofs, we never relied on the f(0) or f(1) terms. Instead, we were referring the limit as n approaches infinity. Therefore, so long as the "rules" of the recursive relationship held up, the outcomes and ratios were the same.

Another reason is that the terms here in the Lucas sequence are really just the same terms of the Fibonnaci sequence, multiplied by 3. Since the sequence is produced by adding terms (no extraneous constants), and the beginning terms (0 and 3) are just the Fibonacci first terms (0 and 1) multiplied by three, then each term that follows, by virtue of the function rule, must be the corresponding Fibonacci term multiplied by 3.

i.e. If a + b = c, then 3a + 3b = 3c.

Since this the case, the ratio of any two terms must be the same (not times 3) as the ratio of the two corresponding Fibonacci terms.

i.e. If a/b = c, then (3a)/(3b) = c.

Therefore, we would expect the same pattern involve Phi in the Lucas sequence as we would find in the Fibonnaci sequence.

Find the Excel spreadsheet used for this exploration here.


Return